﻿using System;
using System.IO;
using System.Data;
using System.Drawing;
using System.Threading;
using System.Windows.Forms;
using System.Collections.Generic;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using SYDZ.WMS.DBUtility;
using SYDZ.WMS.HelpClass;
using ElectronicWMSA.Comm.ClassFiles;
using ElectronicWMSA.Comm.Forms.Message;

namespace ElectronicWMSA.T_WMS.T_PutOut
{
    public partial class PickingVersionFrm : Form
    {
        public PickingVersionFrm()
        {
            InitializeComponent();
        }

        #region 读取文件数据
        DataTable dt_Insert = new DataTable();
        DataTable data = new DataTable();
        INPortExcel inp = new INPortExcel();
        private void btn_SelectFiles_Click(object sender, EventArgs e)
        {
            GetFilesData();
            dataGridView1.DataSource = dt_Insert;
        }
        private void GetFilesData()
        {
            OpenFileDialog fd = new OpenFileDialog();
            fd.Filter = "Microsoft Excel files(*.xls,*.xlsx)|*.xls;*.xlsx";

            if (DialogResult.OK == fd.ShowDialog(this))
            {
                string filename = fd.FileName;
                if (inp.IsFileInUse(filename))
                {
                    MessageBox.Show("文件已打开或被其他程序占用，请检查并关闭占用程序！", "提示");
                    return;
                }
                FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read);
                IWorkbook workbook = null;
                try
                {
                    if (filename.IndexOf(".xlsx") > 0)
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                    else if (filename.IndexOf(".xls") > 0)
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    if (workbook != null)
                    {
                        for (int i = 0; i < workbook.NumberOfSheets; i++)
                        {
                            ISheet sheet = workbook.GetSheetAt(i);

                            bool isHide = workbook.IsSheetHidden(i);
                            if (isHide) continue;

                            #region   读取主目数据

                            //作成日期
                            IRow createRow = sheet.GetRow(5);
                            if (createRow == null)
                                continue;
                            ICell createCell = createRow.GetCell(15);

                            if (createCell == null)
                                continue;

                            string Create_time = createCell.DateCellValue.ToString("yyyy-MM-dd");

                            //装箱单号
                            IRow PoNORow = sheet.GetRow(6);
                            if (PoNORow == null)
                                continue;

                            ICell PoNOCell = PoNORow.GetCell(2);
                            if (PoNOCell == null)
                                continue;
                            string poNo = PoNOCell.StringCellValue.Trim();

                            if (!string.IsNullOrEmpty(poNo))
                            {
                                int num = Convert.ToInt32(DbHelperSQL.GetSingle("SELECT  COUNT(NID)  FROM T_PackingListVersion WHERE  Destination='" + poNo + "' "));
                                if (num != 0)
                                {
                                    MessageClass.error("装箱单号:【" + poNo + "】已导入, 无需再导入！");
                                    return;
                                }
                            }
                            else
                            {
                                MessageClass.error("装箱单号不能为空，请检查导入的Excel格式！");
                                return;
                            }

                            //出厂日
                            IRow factoryRow = sheet.GetRow(7);
                            if (factoryRow == null)
                                continue;

                            ICell factoryCell = factoryRow.GetCell(2);
                            if (factoryCell == null)
                                continue;
                            string factory = factoryCell.DateCellValue.ToString("yyyy-MM-dd");

                            //审核人
                            ICell shenherenCell = factoryRow.GetCell(14);
                            if (shenherenCell == null)
                                continue;
                            string shenheren = shenherenCell.StringCellValue;

                            //确认人
                            ICell querenrenCell = factoryRow.GetCell(15);
                            if (querenrenCell == null)
                                continue;
                            string querenren = querenrenCell.StringCellValue;

                            //作成人
                            ICell zchrenCell = factoryRow.GetCell(16);
                            if (zchrenCell == null)
                                continue;
                            string zchren = zchrenCell.StringCellValue;

                            //目的地
                            IRow destinationRow = sheet.GetRow(8);
                            if (destinationRow == null)
                                continue;

                            ICell destinationCell = destinationRow.GetCell(2);
                            if (destinationCell == null)
                                continue;
                            string destination = destinationCell.StringCellValue.Trim();

                            //走货方式
                            IRow wayRow = sheet.GetRow(9);
                            if (wayRow == null)
                                continue;

                            ICell wayCell = wayRow.GetCell(2);
                            if (wayCell == null)
                                continue;
                            string way = wayCell.StringCellValue;

                            #endregion

                            #region  读取细目

                            //读取细目表
                            int startRow = 13;
                            IRow firstRow = sheet.GetRow(13);

                            DataTable dt = new DataTable();
                            dt.TableName = "WMS_TPackingListDetails";
                            if (firstRow != null)
                            {
                                int cellCount = 21;
                                dt.Columns.Add(new DataColumn("packinglistmain_no"));
                                dt.Columns.Add(new DataColumn("Destination"));
                                dt.Columns.Add(new DataColumn("ProDate"));

                                dt.Columns.Add(new DataColumn("PC_Order_No"));
                                dt.Columns.Add(new DataColumn("Panasonic"));
                                dt.Columns.Add(new DataColumn("Customer"));
                                dt.Columns.Add(new DataColumn("Pallet_No"));
                                dt.Columns.Add(new DataColumn("Pallet_Qty"));
                                dt.Columns.Add(new DataColumn("Qty_PA"));
                                dt.Columns.Add(new DataColumn("TTL_Qty"));
                                dt.Columns.Add(new DataColumn("Price"));
                                dt.Columns.Add(new DataColumn("Amount"));
                                dt.Columns.Add(new DataColumn("L"));
                                dt.Columns.Add(new DataColumn("W"));
                                dt.Columns.Add(new DataColumn("H"));
                                dt.Columns.Add(new DataColumn("Total_NW"));
                                dt.Columns.Add(new DataColumn("Total_GW"));
                                dt.Columns.Add(new DataColumn("Total_VOL"));
                                dt.Columns.Add(new DataColumn("L2"));
                                dt.Columns.Add(new DataColumn("W2"));
                                dt.Columns.Add(new DataColumn("H2"));
                                dt.Columns.Add(new DataColumn("NW2"));
                                dt.Columns.Add(new DataColumn("GW2"));
                                dt.Columns.Add(new DataColumn("way"));
                                dt.Columns.Add(new DataColumn("Create_Time"));
                                dt.Columns.Add(new DataColumn("Update_Time"));
                                dt.Columns.Add(new DataColumn("Update_user_id"));

                                //最后一列的标号
                                int rowCount = startRow + 1;
                                for (int k = startRow + 1; ; k++)
                                {
                                    IRow row = sheet.GetRow(k);
                                    if (row.GetCell(2) != null)
                                    {
                                        string stringValue = row.GetCell(2).StringCellValue;
                                        if (string.Equals("Total", stringValue.Trim()))
                                        {
                                            rowCount = k;
                                            break;
                                        }
                                    }

                                }
                                for (int k = startRow + 1; k < rowCount - 1; k++)
                                {
                                    IRow row = sheet.GetRow(k);
                                    if (row == null) continue; //没有数据的行默认是null　　　　　　　

                                    //解决板号格式为1-2的导入问题
                                    ICell icell = row.GetCell(4);
                                    if (icell == null) continue;
                                    icell.SetCellType(CellType.String);
                                    string iValue = icell.StringCellValue;
                                    if (string.IsNullOrEmpty(iValue) || string.IsNullOrEmpty(iValue.Trim())) continue;

                                    int icount = 1;
                                    int startNumber = 0;
                                    if (iValue.Contains("-"))
                                    {
                                        string[] nString = iValue.Split('-');
                                        icount = int.Parse(nString[1].Trim()) - int.Parse(nString[0].Trim()) + 1;
                                        startNumber = int.Parse(nString[0].Trim());
                                    }
                                    else
                                    {
                                        startNumber = int.Parse(iValue.Trim());
                                    }

                                    for (int s = 0; s < icount; s++)
                                    {
                                        DataRow dataRow = dt.NewRow();

                                        for (int j = 1; j < cellCount; j++)
                                        {
                                            //同理，没有数据的单元格都默认是null
                                            if (row.GetCell(j) != null)
                                            {
                                                if (row.GetCell(j).CellType == CellType.Formula)
                                                {
                                                    switch (j)
                                                    {
                                                        case 4:
                                                            dataRow[j + 2] = startNumber + s;
                                                            break;
                                                        case 5:
                                                        case 7:
                                                            int iNumber = (int)row.GetCell(j).NumericCellValue;
                                                            dataRow[j + 2] = (iNumber / icount);
                                                            break;
                                                        case 9:
                                                        case 13:
                                                        case 14:
                                                        case 15:
                                                            double iDNumber = row.GetCell(j).NumericCellValue;
                                                            dataRow[j + 2] = (iDNumber / icount);
                                                            break;
                                                        default:
                                                            double nValue = row.GetCell(j).NumericCellValue;
                                                            dataRow[j + 2] = nValue;
                                                            break;
                                                    }
                                                }
                                                else
                                                {
                                                    string strValue = row.GetCell(j).ToString();
                                                    if ((string.IsNullOrEmpty(strValue) || string.IsNullOrEmpty(strValue.Trim())) && j >= 4 && j <= 20)
                                                    {
                                                        dataRow[j + 2] = 0;
                                                    }
                                                    else
                                                    {
                                                        switch (j)
                                                        {
                                                            case 4:
                                                                dataRow[j + 2] = startNumber + s;
                                                                break;
                                                            case 5:
                                                            case 7:
                                                                int iNumber = (int)row.GetCell(j).NumericCellValue;
                                                                dataRow[j + 2] = (iNumber / icount);
                                                                break;
                                                            case 9:
                                                            case 13:
                                                            case 14:
                                                            case 15:
                                                                double iDNumber = row.GetCell(j).NumericCellValue;
                                                                dataRow[j + 2] = (iDNumber / icount);
                                                                break;
                                                            default:
                                                                dataRow[j + 2] = row.GetCell(j).ToString();
                                                                break;
                                                        }
                                                    }
                                                }
                                            }
                                            else
                                            {
                                                if (j >= 4 && j <= 20)
                                                {
                                                    dataRow[j + 2] = 0;
                                                }
                                            }
                                        }
                                        dataRow[24] = DateTime.Now.ToString();
                                        dataRow[25] = DateTime.Now.ToString();
                                        dataRow[26] = SystemVariable.User_ID.ToString();
                                        dataRow[0] = poNo;
                                        dataRow[1] = factory;
                                        dataRow[2] = destination;
                                        dataRow[23] = way;

                                        dt.Rows.Add(dataRow);
                                    }
                                }
                            }
                            #endregion

                            dt_Insert = dt;
                        }
                    }
                    else
                    {
                        MessageClass.error("获取文件数据失败！");
                    }
                }
                catch (Exception ex)
                {
                    MessageClass.error(ex.Message);
                }
                finally
                {
                    if (fs != null)
                    {
                        fs.Close();
                    }
                }
            }
        }
        private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            SolidBrush b = new SolidBrush(dataGridView1.RowHeadersDefaultCellStyle.ForeColor);
            e.Graphics.DrawString((e.RowIndex + 1).ToString(System.Globalization.CultureInfo.CurrentUICulture), dataGridView1.DefaultCellStyle.Font, b, e.RowBounds.Location.X + 20, e.RowBounds.Location.Y + 4);
        }
        #endregion
        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        List<string> list = new List<string>();
        DataSet ds_Version = new DataSet();
        DataTable dt_PKNO = new DataTable();
        private void btn_Import_Click(object sender, EventArgs e)
        {
            list = new List<string>();
            if (dt_Insert.Rows.Count > 0)
            {
                #region 
                dt_PKNO = new DataTable();
                dt_PKNO.TableName = "Packing_TB";
                dt_PKNO.Columns.Add(new DataColumn("PKNO"));
                dt_PKNO.Columns.Add(new DataColumn("PONO"));
                dt_PKNO.Columns.Add(new DataColumn("Destination"));
                dt_PKNO.Columns.Add(new DataColumn("OutDate"));
                dt_PKNO.Columns.Add(new DataColumn("ProductModel"));
                dt_PKNO.Columns.Add(new DataColumn("SerialNumber"));
                dt_PKNO.Columns.Add(new DataColumn("VersionNO"));
                dt_PKNO.Columns.Add(new DataColumn("BoardNO"));
                dt_PKNO.Columns.Add(new DataColumn("Qty"));
                dt_PKNO.Columns.Add(new DataColumn("TotalWeight"));
                foreach (DataRow dr in dt_Insert.Rows)
                {
                    DataRow dataRow = dt_PKNO.NewRow();
                    dataRow[0] = dr["packinglistmain_no"].ToString();
                    dataRow[1] = dr["PC_Order_No"].ToString();
                    dataRow[2] = dr["ProDate"].ToString();
                    dataRow[3] = dr["Destination"].ToString();
                    dataRow[4] = dr["Customer"].ToString();
                    dataRow[5] = "";
                    dataRow[6] = "";
                    dataRow[7] = Convert.ToInt32(dr["Pallet_No"].ToString() == string.Empty ? "0" : dr["Pallet_No"].ToString());
                    dataRow[8] = Convert.ToInt32(dr["Qty_PA"].ToString() == string.Empty ? "0" : dr["Qty_PA"].ToString());
                    dataRow[9] = Convert.ToDecimal(dr["Total_GW"].ToString() == string.Empty ? "0" : dr["Total_GW"].ToString());
                    dt_PKNO.Rows.Add(dataRow);
                }
                #endregion
                string PKNO = dt_PKNO.Rows[0]["PKNO"].ToString();
                if (PKNO == "")
                {
                    MessageClass.error("未能读取到装箱单号！"); return;
                }
                if (!GetPKNO(PKNO))
                {
                    if (MessageClass.enquire("确认导入信息？") == DialogResult.OK)
                    {
                        WaitingBoxFrm wf = new WaitingBoxFrm((obj, args) => { Thread.Sleep(500); ImportData(); }, 5000); wf.ShowDialog(this);
                    }
                }
                else
                {
                    if (MessageClass.enquire("此装箱单号已经导入，重新导入将删除原来的信息，是否重新导入？") == DialogResult.OK)
                    {
                        DbHelperSQL.ExecuteSql(" UPDATE [DB_WMSFIRST].[dbo].[T_Stock] SET BK13=NULL WHERE BK13='" + PKNO + "'");
                        DbHelperSQL.ExecuteSql(" DELETE FROM [DB_WMSFIRST].[dbo].[T_PackingListVersion] WHERE PKNO='" + PKNO + "'");

                        WaitingBoxFrm wf = new WaitingBoxFrm((obj, args) => { Thread.Sleep(500); ImportData(); }, 5000); wf.ShowDialog(this);
                    }
                }
                if (ImportQry > 0)
                {
                    if (ImportQry == ImportQryEd)
                    {
                        if (DbHelperSQL.ExecuteSqlTran(list) == 0)
                        {
                            MessageClass.warning("操作失败！");
                        }
                        else
                        {
                            MessageClass.right("操作成功!");
                            SystemVariable.AddLogInfo("版本号管理-导入装箱单", "用户：【" + SystemVariable.Login_Name + "操作了导入装箱单数据：" + PKNO);
                        }
                    }
                    else
                    {
                        MessageClass.warning("机种在库数不满足装箱单号机种数量，请检查库存！");
                    }
                }
            }
        }

        /// <summary>
        /// 实际需导入的台数
        /// </summary>
        private int ImportQry = 0;
        /// <summary>
        /// 库存匹配台数
        /// </summary>
        private int ImportQryEd = 0;
        /// <summary>
        /// 导入数据逻辑
        /// </summary>
        private void ImportData()
        {
            try
            {
                ds_Version = DbHelperSQL.Query("SELECT NID,Product_Model,Serial_Number,BK03,BK12,BK13,Update_Time AS InTime FROM [DB_WMSFIRST].[dbo].[T_Stock] WHERE BK03!='Option' AND  BK12 IS NOT NULL  AND  PickList_NO IS  NULL AND BK03!='1'  AND BK11 IS NULL ORDER BY Update_Time ASC");
                if (ds_Version.Tables[0].Rows.Count == 0)
                {
                    MessageClass.warning("无库存使用！"); return;
                }
                string _PKNO = "";
                string _PONO = "";
                string _Destination = "";
                string _OutDate = "";
                string _ProductModel = "";
                string _SerialNumber = "";
                string _VersionNO = "";
                int _BoardNO = 0;
                int _Qty = 0;
                double _TotalWeight = 0;
                string _CreateUser = SystemVariable.Login_Name;
                DateTime InTime = DateTime.Now;
                list = new List<string>();

                ImportQry = 0;
                ImportQryEd = 0;

                for (int i = 0; i < dt_PKNO.Rows.Count; i++)
                {
                    int _Num = Convert.ToInt32(dt_PKNO.Rows[i]["Qty"].ToString());
                    _PKNO = dt_PKNO.Rows[i]["PKNO"].ToString();
                    _PONO = dt_PKNO.Rows[i]["PONO"].ToString();
                    _Destination = dt_PKNO.Rows[i]["Destination"].ToString();
                    _OutDate = dt_PKNO.Rows[i]["OutDate"].ToString();
                    _ProductModel = dt_PKNO.Rows[i]["ProductModel"].ToString();
                    _BoardNO = Convert.ToInt32(dt_PKNO.Rows[i]["BoardNO"].ToString() == string.Empty ? "0" : dt_PKNO.Rows[i]["BoardNO"].ToString());
                    _Qty = Convert.ToInt32(dt_PKNO.Rows[i]["Qty"].ToString() == string.Empty ? "0" : dt_PKNO.Rows[i]["Qty"].ToString());
                    _TotalWeight = Convert.ToDouble(dt_PKNO.Rows[i]["TotalWeight"].ToString() == string.Empty ? "0" : dt_PKNO.Rows[i]["TotalWeight"].ToString());

                    ImportQry = ImportQry + _Qty;

                    string _BK13_PKNO_Version = "";
                    string _ProductModel_Version = "";
                    string _BK12_Version = "";
                    string _NID_Version = "0";
                    int numCount = 0;
                    for (int k = 0; k < ds_Version.Tables[0].Rows.Count; k++)
                    {
                        _BK12_Version = ds_Version.Tables[0].Rows[k]["BK12"].ToString().Trim();
                        _BK13_PKNO_Version = ds_Version.Tables[0].Rows[k]["BK13"].ToString().Trim();
                        _ProductModel_Version = ds_Version.Tables[0].Rows[k]["Product_Model"].ToString().Trim();
                        _NID_Version = ds_Version.Tables[0].Rows[k]["NID"].ToString().Trim();

                        if (_ProductModel == _ProductModel_Version)
                        {
                            if (_BK12_Version != "" && _BK13_PKNO_Version == "")
                            {
                                _SerialNumber = ds_Version.Tables[0].Rows[k]["Serial_Number"].ToString().Trim();
                                _VersionNO = _BK12_Version;

                                list.Add(" INSERT INTO [DB_WMSFIRST].[dbo].[T_PackingListVersion](PKNO,PONO,Destination,OutDate,ProductModel,SerialNumber,VersionNO,BoardNO,Qty,TotalWeight,CreateTime,CreateUser) VALUES ('" + _PKNO + "','" + _PONO + "','" + _Destination + "','" + _OutDate + "','" + _ProductModel + "','" + _SerialNumber + "','" + _VersionNO + "','" + _BoardNO + "','" + _Qty + "','" + _TotalWeight + "','" + InTime + "','" + _CreateUser + "')");

                                list.Add(" UPDATE [DB_WMSFIRST].[dbo].[T_Stock] SET BK13='" + _PKNO + "' WHERE NID='" + _NID_Version + "'");

                                DataRow row = ds_Version.Tables[0].Rows[k];
                                row.BeginEdit();
                                row["BK13"] = _PKNO;
                                row.EndEdit();
                                ds_Version.Tables[0].AcceptChanges();

                                numCount++;

                                ImportQryEd++;

                                if (numCount == _Num)
                                {
                                    break;
                                }
                            }
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                ErrorLogLoad.ErrorLogInfo(ex.Message.ToString());
            }
        }
        private bool GetPKNO(string _PKNO)
        {
            string sql = " SELECT  PKNO FROM [DB_WMSFIRST].[dbo].[T_PackingListVersion] WHERE PKNO='" + _PKNO + "' ";
            DataTable dt = DbHelperSQL.ExecuteDataTable(sql);
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}
